package com.yun.util.clickHouse.base;


import com.yun.util.clickHouse.annotation.CkTable;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import javax.sql.DataSource;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;


@Component
public abstract class ClickDao<PK extends Serializable, T extends BaseEntity> implements ClickIDao<PK, T> {
    final Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    @Qualifier("clickhouseDataSource")
    private DataSource dataSource;
    private final Class<T> clazz;

    @SuppressWarnings("unchecked")
    public ClickDao(){
        this.clazz =(Class<T>) ( (ParameterizedType) this.getClass().getGenericSuperclass()).getActualTypeArguments()[1];
    }
    @Override
    public List<JSONObject> exeSql(String sql){
        logger.info("cliockhouse 执行sql：" + sql);
        ClickHouseJDBC clickHouseJDBC = new ClickHouseJDBC(dataSource);
        try (Connection connection = clickHouseJDBC.getConn();){
            Statement statement = connection.createStatement();
            ResultSet results = statement.executeQuery(sql);
            ResultSetMetaData rsmd = results.getMetaData();
            List<JSONObject> list = new ArrayList<>();
            while(results.next()){
                JSONObject row = new JSONObject();
                for(int i = 1;i<=rsmd.getColumnCount();i++){
                    row.put(rsmd.getColumnName(i),results.getString(rsmd.getColumnName(i)));
                }
                list.add(row);
            }

            return list;
        } catch (Exception e) {
           logger.error(e.getMessage(),e);
        }finally {
            clickHouseJDBC.close();
        }
        return null;
    }

    @Override
    public void save(T entity) {

        ClickHouseJDBC clickHouseJDBC = new ClickHouseJDBC(dataSource);
        String tableName = getTableName(entity.getClass());
        ArrayList<String> fieldColumnNameList = null;
        ArrayList<Object> fieldValueList = null;
        ArrayList<Class<?>> fieldTypeList = null;
        ArrayList dataArray[] ;
        try {
            dataArray = getField(entity);
            fieldColumnNameList = dataArray[0];
            fieldValueList =dataArray[1];
            fieldTypeList = dataArray[2];
        }catch (IllegalAccessException e){
            logger.error(e.getMessage());
        }
        StringBuffer sql = new StringBuffer();
        sql.append("insert into ")
                .append(tableName)
                .append(" (");
        for (String field : fieldColumnNameList) {
            sql.append(field).append(",");
        }
        sql.setLength(sql.length()-1);
        sql.append(") values (");
        for (int i = 0; i < fieldValueList.size(); i++) {
            sql.append("?").append(",");
        }
        sql.setLength(sql.length()-1);
        sql.append(")");
        for (int i = 0; i < fieldTypeList.size(); i++) {
            if (fieldTypeList.get(i).isAssignableFrom(int.class))
                sql.replace(sql.indexOf("?"),sql.indexOf("?")+1,fieldValueList.get(i) == null ? "" :String.valueOf(fieldValueList.get(i)));
            if (fieldTypeList.get(i).isAssignableFrom(String.class))
                sql.replace(sql.indexOf("?"),sql.indexOf("?")+1,fieldValueList.get(i) == null ? "\'\'" :"\'"+fieldValueList.get(i)+"\'");
            if (fieldTypeList.get(i).isAssignableFrom(Date.class))
                sql.replace(sql.indexOf("?"),sql.indexOf("?")+1,fieldValueList.get(i) == null ? "\'\'" :"\'"+fieldValueList.get(i)+"\'");

        }
        try {
            clickHouseJDBC.executeSql(sql.toString());
            clickHouseJDBC.close();
        }catch (SQLException e){
            logger.error(e.getMessage());
        }
    }

    @Override
    public void batchSave(List<T> entityList) {
        if(entityList==null||entityList.isEmpty()){
            return;
        }
        ClickHouseJDBC clickHouseJDBC = new ClickHouseJDBC(dataSource);
        try{
            String tableName = getTableName(entityList.get(0).getClass());
            ArrayList<String> fieldColumnNameList = null;
            ArrayList<String> fieldValueList = null;
            ArrayList<Class<?>> fieldTypeList = null;
            ArrayList dataArray[] ;
            List<List> valueList = new ArrayList<>();
            List<List<Class<?>>> typeList = new ArrayList<>();
            for (T entity : entityList) {

                try {
                    dataArray = getField(entity);
                    fieldColumnNameList = dataArray[0];
                    fieldValueList = dataArray[1];
                    fieldTypeList = dataArray[2];
                } catch (IllegalAccessException e) {
                    System.out.println("获取失败");
                }
                valueList.add(fieldValueList);
                typeList.add(fieldTypeList);
            }
            StringBuffer sql = new StringBuffer();
            sql.append("insert into ")
                    .append(tableName)
                    .append(" (");
            for (String field : fieldColumnNameList) {
                sql.append(field).append(",");
            }
            sql.setLength(sql.length() - 1);
            sql.append(") values (");
            for (int i = 0; i < fieldValueList.size(); i++) {
                sql.append("?").append(",");
            }
            sql.setLength(sql.length() - 1);
            sql.append(") ");

            clickHouseJDBC.batchExecuteSql(sql.toString(),valueList,typeList);
        }catch (Exception e){
            logger.error(e.getMessage(),e);
        }finally {
            clickHouseJDBC.close();
        }

    }

    @Override
    public void deleteById(String id) {
        ClickHouseJDBC clickHouseJDBC = new ClickHouseJDBC(dataSource);
        String tableName = getTableName(clazz);
        StringBuffer sql = new StringBuffer();
        sql.append("alter table ")
                .append(tableName)
                .append(" delete where ID =  \'")
                .append(id)
                .append("\' ");
        try {
            clickHouseJDBC.deleteSql(sql.toString());
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            clickHouseJDBC.close();
        }

    }
    @Override
    public List<T> find(String sql){
        ClickHouseJDBC clickHouseJDBC = new ClickHouseJDBC(dataSource);
        List<T> result = new ArrayList<>();
        ArrayList[] dataArray ;
        T instance = null;
        try {
            instance = clazz.newInstance();
            dataArray = getField(instance);
            ArrayList<String> fieldColumnNameList = dataArray[0];
            ArrayList<Class<?>> fieldTypeList = dataArray[2];
            ArrayList<String> fieldNameList = dataArray[3];
            ArrayList fields = dataArray[4];
            ResultSet resultSet = clickHouseJDBC.executeSql(sql);
            while (resultSet.next()){
                T temp = clazz.newInstance();
                for (int i = 0; i < fields.size(); i++) {
                    //获取字段的名称
                    String name = fieldNameList.get(i);
                    //获取字段的类型
                    Class<?> type = fieldTypeList.get(i);
                    // 首字母大写
                    String replace = name.substring(0, 1).toUpperCase()
                            + name.substring(1);
                    //获得setter方法
                    Method setMethod = clazz.getMethod("set" + replace, type);


                    // ---判断读取数据的类型
                    if (type.isAssignableFrom(String.class)) {
                        setMethod.invoke(temp, resultSet.getString(fieldColumnNameList.get(i)));
                    } else if (type.isAssignableFrom(int.class) || type.isAssignableFrom(Integer.class)) {
                        setMethod.invoke(temp, resultSet.getInt(fieldColumnNameList.get(i)));
                    }else if (type.isAssignableFrom(float.class) || type.isAssignableFrom(Float.class)) {
                        setMethod.invoke(temp, resultSet.getFloat(fieldColumnNameList.get(i)));
                    }else if (type.isAssignableFrom(double.class) || type.isAssignableFrom(Double.class)) {
                        setMethod.invoke(temp, resultSet.getDouble(fieldColumnNameList.get(i)));
                    }else if (type.isAssignableFrom(boolean.class) || type.isAssignableFrom(Boolean.class)) {
                        setMethod.invoke(temp, resultSet.getBoolean(fieldColumnNameList.get(i)));
                    }else if (type.isAssignableFrom(byte.class) || type.isAssignableFrom(Byte.class)) {
                        setMethod.invoke(temp, resultSet.getByte(fieldColumnNameList.get(i)));
                    }else if (type.isAssignableFrom(short.class) || type.isAssignableFrom(Short.class)) {
                        setMethod.invoke(temp, resultSet.getShort(fieldColumnNameList.get(i)));
                    }else if (type.isAssignableFrom(long.class) || type.isAssignableFrom(Long.class)) {
                        setMethod.invoke(temp, resultSet.getLong(fieldColumnNameList.get(i)));
                    }else if (type.isAssignableFrom(Date.class)) {
                        String value = resultSet.getString(fieldColumnNameList.get(i));
                        if (value == null){
                            setMethod.invoke(temp,value);
                        }else {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            setMethod.invoke(temp,sdf.parse(value));
                        }
                    }else if (type.isAssignableFrom(Timestamp.class)) {
                        String value = resultSet.getString(fieldColumnNameList.get(i));
                        if (value == null){
                            setMethod.invoke(temp,value);
                        }else {
                            setMethod.invoke(temp,Timestamp.valueOf(value));
                        }
                    }
                }
                result.add(temp);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("SQL错误");
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {
            clickHouseJDBC.close();
        }
        return result;
    }

    @Override
    public void update(T entity){
        ClickHouseJDBC clickHouseJDBC = new ClickHouseJDBC(dataSource);
        String tableName = getTableName(clazz);
        ArrayList<String> fieldColumnNameList = null;
        ArrayList<String> fieldValueList = null;
        ArrayList<Class<?>> fieldTypeList = null;
        ArrayList dataArray[] ;
        try {
            dataArray = getField(entity);
            fieldColumnNameList = dataArray[0];
            fieldValueList =dataArray[1];
            fieldTypeList = dataArray[2];
        }catch (IllegalAccessException e){
            System.out.println("获取失败");
        }
        String id = "";
        for (int i = 0; i < fieldColumnNameList.size(); i++) {
            if (fieldColumnNameList.get(i).equals("id") || fieldColumnNameList.get(i).equals("ID")){
                if (fieldValueList.get(i) == null || String.valueOf(fieldValueList.get(i)).equals(""))
                    throw new RuntimeException("主键为空");
                id = String.valueOf(fieldValueList.get(i));
                fieldColumnNameList.remove(i);
                fieldValueList.remove(i);
                fieldTypeList.remove(i);
            }
        }
        int temp = fieldValueList.size();
        while (temp >= 0){
            for (int i = 0; i < fieldValueList.size(); i++) {
                if (fieldValueList.get(i) == null){
                    fieldColumnNameList.remove(i);
                    fieldValueList.remove(i);
                    fieldTypeList.remove(i);
                    break;
                }
            }
            temp--;
        }

        StringBuffer sql = new StringBuffer();
        sql.append("alter table ")
                .append(tableName)
                .append(" update ");
        for (String columnName : fieldColumnNameList) {
            sql.append(columnName).append(" = ? ,");
        }
        sql.setLength(sql.length()-1);
        sql.append(" where ID = \'").append(id).append("\' ");
        try {
            clickHouseJDBC.setParamExecuteSql(sql.toString(),fieldValueList,fieldTypeList);
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    private String getTableName(Class<? extends BaseEntity> clazz){
        return clazz.getAnnotation(CkTable.class).name();
    }

    private ArrayList[] getField(T entity) throws IllegalAccessException {
        ArrayList[] result = new ArrayList[5];
        ArrayList<String> fieldColumnNameList = new ArrayList();
        ArrayList<Object> fieldValueList = new ArrayList();
        ArrayList<String> fieldNameList = new ArrayList();
        ArrayList<Class<?>> fieldTypeList = new ArrayList();
        ArrayList<Field> fieldsList = new ArrayList<>();
        boolean flag = true;
        Field[] fields = entity.getClass().getDeclaredFields();
        for (Field f : fields) {
            if (f.isAnnotationPresent(Column.class) && !f.isAnnotationPresent(Id.class)) {
                fieldsList.add(f);
                f.setAccessible(true);
                fieldColumnNameList.add(f.getAnnotation(Column.class).name());
                fieldTypeList.add(f.getType());
                if (f.get(entity) !=null && f.getType().isAssignableFrom(Date.class)){
//                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    Date date1 = (Date) f.get(entity);
                    java.sql.Date date = new java.sql.Date(date1.getTime());
                    fieldValueList.add(date);
                }else
                    fieldValueList.add(f.get(entity));
                fieldNameList.add(f.getName());
            }
            if (f.isAnnotationPresent(Id.class) && flag) {
                fieldsList.add(f);
                f.setAccessible(true);
                fieldNameList.add(f.getName());
                fieldColumnNameList.add(f.getAnnotation(Column.class).name());
                fieldTypeList.add(f.getType());
                fieldValueList.add(f.get(entity) == null ||f.get(entity).equals("") ? UUID.randomUUID().toString().replaceAll("-", "") : f.get(entity));
                flag = false;
            }
        }
        Class superClass = entity.getClass().getSuperclass();
        while (superClass != Object.class){
            if (!superClass.isAnnotationPresent(MappedSuperclass.class))
                continue;
            Field[] superFields = superClass.getDeclaredFields();
            for (Field f : superFields) {
                if (f.isAnnotationPresent(Column.class) && !f.isAnnotationPresent(Id.class)) {
                    fieldsList.add(f);
                    f.setAccessible(true);
                    fieldColumnNameList.add(f.getAnnotation(Column.class).name());
                    fieldTypeList.add(f.getType());
                    if (f.get(entity) !=null && f.getType().isAssignableFrom(Date.class)){
//                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date1 = (Date) f.get(entity);
                        java.sql.Date date = new java.sql.Date(date1.getTime());
                        fieldValueList.add(date);
                    }else
                        fieldValueList.add(f.get(entity));
                    fieldNameList.add(f.getName());
                }
                if (f.isAnnotationPresent(Id.class) && flag) {
                    fieldsList.add(f);
                    f.setAccessible(true);
                    fieldNameList.add(f.getName());
                    fieldColumnNameList.add(f.getAnnotation(Column.class).name());
                    fieldTypeList.add(f.getType());
                    fieldValueList.add(f.get(entity) == null ||f.get(entity).equals("") ? UUID.randomUUID().toString().replaceAll("-", "") : f.get(entity));
                    flag = false;
                }
            }
            superClass = superClass.getSuperclass();
        }
        result[0] = fieldColumnNameList;
        result[1] = fieldValueList;
        result[2] = fieldTypeList;
        result[3] = fieldNameList;
        result[4] = fieldsList;
        return result;
    }

}
